/**
 * 北谷信息科技（广州）有限公司拥有本软件所有版权 © 2022，并保留所有权利。
 * Copyright © 2022, North Valley Information Technology (Guangzhou) Company
 * Limited, All Rights Reserved.
 */

package main.com.scrapy.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author Double
 * <p><b>Creation Time:</b> 2022/9/17 11:21</p>
 * @since scrapy-1688
 */
public class ExcelUtil {

  //  private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

  private final static String XLSX = "xlsx";
  private final static String XLS = "xls";

  /**
   * 读入excel文件，解析后返回
   *
   * @param file
   * @throws IOException
   */
  public static List<String[]> readExcel(File file) throws IOException {
    //检查文件
    checkFile(file);
    //获得Workbook工作薄对象
    Workbook workbook = getWorkbok(file);
    //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
    List<String[]> list = new ArrayList<String[]>();
    if (workbook != null) {
      for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
        //获得当前sheet工作表
        Sheet sheet = workbook.getSheetAt(sheetNum);
        if (sheet == null) {
          continue;
        }
        //获得当前sheet的开始行
        int firstRowNum = sheet.getFirstRowNum();
        //获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        //循环除了第一行的所有行
        for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) { //为了过滤到第一行因为我的第一行是数据库的列
          //获得当前行
          Row row = sheet.getRow(rowNum);
          if (row == null) {
            continue;
          }
          //获得当前行的开始列
          int firstCellNum = row.getFirstCellNum();
          //获得当前行的列数
          int lastCellNum = row.getLastCellNum();//为空列获取
          //                    int lastCellNum = row.getPhysicalNumberOfCells();//为空列不获取
          //                    String[] cells = new String[row.getPhysicalNumberOfCells()];
          String[] cells = new String[row.getLastCellNum()];
          //循环当前行
          for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
            Cell cell = row.getCell(cellNum);
            //            cells[cellNum] = getCellValue(cell);
          }
          list.add(cells);
        }
      }
    }
    //    log.info(gson.toJson(list));
    return list;
  }

  public static Workbook getWorkbok(File file) throws IOException {
    Workbook wb = null;
    FileInputStream in = new FileInputStream(file);
    if (file.getName().endsWith(XLS)) {     //Excel&nbsp;2003
      wb = new HSSFWorkbook(in);
    } else if (file.getName().endsWith(XLSX)) {    // Excel 2007/2010
      wb = new XSSFWorkbook(in);
    }
    return wb;
  }

  public static void checkFile(File file) throws IOException {
    //判断文件是否存在
    if (null == file) {
      throw new FileNotFoundException("文件不存在！");
    }
    //获得文件名
    String fileName = file.getName();
    //判断文件是否是excel文件
    if (!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
      throw new IOException(fileName + "不是excel文件");
    }
  }

  public static void writeExcel(List<Map> dataList, int cloumnCount, String finalXlsxPath) {
    OutputStream out = null;
    try {
      // 获取总列数
      int columnNumCount = cloumnCount;
      // 读取Excel文档
      File finalXlsxFile = new File(finalXlsxPath);
      Workbook workBook = getWorkbok(finalXlsxFile);
      // sheet 对应一个工作页
      Sheet sheet = workBook.getSheetAt(0);
      /**
       * 删除原有数据，除了属性列
       */
      int rowNumber = sheet.getLastRowNum();    // 第一行从0开始算
      System.out.println("原始数据总行数，除属性列：" + rowNumber);
      for (int i = 1; i <= rowNumber; i++) {
        Row row = sheet.getRow(i);
        sheet.removeRow(row);
      }
      // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
      out = new FileOutputStream(finalXlsxPath);
      workBook.write(out);
      /**
       * 往Excel中写新数据
       */
      for (int j = 0; j < dataList.size(); j++) {
        // 创建一行：从第二行开始，跳过属性列
        Row row = sheet.createRow(j + 1);
        // 得到要插入的每一条记录
        Map dataMap = dataList.get(j);
        String name = dataMap.get("BankName").toString();
        String address = dataMap.get("Addr").toString();
        String phone = dataMap.get("Phone").toString();
        for (int k = 0; k <= columnNumCount; k++) {
          // 在一行内循环
          Cell first = row.createCell(0);
          first.setCellValue(name);

          Cell second = row.createCell(1);
          second.setCellValue(address);

          Cell third = row.createCell(2);
          third.setCellValue(phone);
        }
      }
      // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
      out = new FileOutputStream(finalXlsxPath);
      workBook.write(out);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (out != null) {
          out.flush();
          out.close();
        }
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
    System.out.println("数据导出成功");
  }

}
